using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using Antix;
using Antix.Data;

namespace Antix.Data.Catalogue.Objects.SqlServer
{
    /// <summary>
    /// Database
    /// </summary>
    public class SqlDatabase : Database
    {
        #region Constructor/Defaults

        /// <summary>
        /// Create a new database
        /// </summary>
        public SqlDatabase(string connectionString)
            : base(GetDatabaseName(connectionString), GetServerName(connectionString), connectionString) { }

        #endregion

        #region Constants

        #endregion

        #region Methods

        #region GetDatabaseName

        /// <summary>
        /// <para>find the database name in a connection string</para>
        /// </summary>
        /// <param name="connectionString">Connection string</param>
        /// <returns>Name if found, null otherwise</returns>
        public static string GetDatabaseName(string connectionString)
        {
            foreach (string item in connectionString.Split(';'))
            {
                string[] itemSplit = item.Split('=');
                switch (itemSplit[0].ToLower())
                {
                    case "database":
                    case "initial catalog":
                    case "attachdbfilename":
                        return itemSplit[1];
                }
            }

            return null;
        }

        /// <summary>
        /// <para>find the server name in a connection string</para>
        /// </summary>
        /// <param name="connectionString">Connection string</param>
        /// <returns>Server name if found, null otherwise</returns>
        public static string GetServerName(string connectionString)
        {
            foreach (string item in connectionString.Split(';'))
            {
                string[] itemSplit = item.Split('=');
                switch (itemSplit[0].ToLower())
                {
                    case "server":
                    case "data source":
                        return itemSplit[1];
                }
            }

            return null;
        }

        #endregion

        #region Exists

        /// <summary>
        /// <para>Check for existance by attempting connection</para>
        /// </summary>
        public override bool Exists(string connectionString)
        {
            SqlConnection conn = new SqlConnection(this.ConnectionString);
            try
            {
                conn.Open();
                conn.Close();

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        #endregion

        #region Script the database

        /// <summary>
        /// Generate script for this database
        /// </summary>
        /// <param name="sql">Sql</param> 
        public override bool Script(StringBuilder sql, bool subObjects)
        {
            bool scriptCreated = false;

            if (this.IsNew)
            {
                sql.Append("CREATE DATABASE [");
                sql.Append(this.Name);
                sql.Append("]\nGO\n");

                scriptCreated = true;
            }
            int sqlLen = sql.Length;

            #region TODO: Rename

            //else if(this.IsEdited)
            //{
            //    // put in single user mode
            //    sql.Append("ALTER DATABASE [");
            //    sql.Append(this.NameOriginal);
            //    sql.Append("] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
            //    sql.Append("\nGO\n");

            //    // change the name
            //    sql.Append("ALTER DATABASE [");
            //    sql.Append(this.NameOriginal);
            //    sql.Append("] MODIFY NAME=[");
            //    sql.Append(this.Name);
            //    sql.Append("]\nGO\n");

            //    // change the file names
            //    sql.Append("ALTER DATABASE [");
            //    sql.Append(this.Name);
            //    sql.Append("] MODIFY FILE (name='");
            //    sql.Append(this.NameOriginal);
            //    sql.Append("', newname='");
            //    sql.Append(this.Name);
            //    sql.Append("', filename='");
            //    sql.Append(this.Server.SQLPath);
            //    sql.Append("Data\\");
            //    sql.Append(this.Name);
            //    sql.Append(".mdf')\n");

            //    sql.Append("ALTER DATABASE [");
            //    sql.Append(this.Name);
            //    sql.Append("] MODIFY FILE (name='");
            //    sql.Append(this.NameOriginal);
            //    sql.Append("_log', newname='");
            //    sql.Append(this.Name);
            //    sql.Append("_log', filename='");
            //    sql.Append(this.Server.SQLPath);
            //    sql.Append("Data\\");
            //    sql.Append(this.Name);
            //    sql.Append("_log.ldf')\nGO\n");

            //    // put in multi user mode
            //    sql.Append("ALTER DATABASE [");
            //    sql.Append(this.Name);
            //    sql.Append("] SET MULTI_USER");
            //    sql.Append("\nGO\n");
            //}
            #endregion

            if (subObjects)
            {
                // script the schemas
                if (this.Schemas.Script(sql, subObjects)) { scriptCreated = true; }

                // script the tables
                if (this.Tables.Script(sql, subObjects)) { scriptCreated = true; }

                // script the references
                if (this.References.Script(sql, subObjects)) { scriptCreated = true; }

                if (scriptCreated)
                {
                    sql.Insert(sqlLen, "]\nGO\n");
                    sql.Insert(sqlLen, this.Name);
                    sql.Insert(sqlLen, "USE [");
                }
            }

            return scriptCreated;
        }

        #region Script a schema

        /// <summary>
        /// Generate script for this Schema
        /// </summary>
        /// <param name="sql">Sql</param>
        public override bool Script(DatabaseSchema schema, StringBuilder sql, bool subObjects)
        {
            bool scriptCreated = false;

            if (schema.IsNew
                && !"dbo".Equals(schema.Name, StringComparison.CurrentCultureIgnoreCase))
            {
                sql.Append("CREATE SCHEMA [");
                sql.Append(schema.Name);
                sql.Append("]\nGO\n");

                scriptCreated = true;
            }

            return scriptCreated;
        }

        #endregion

        #region Script an index

        /// <summary>
        /// Generate script for this index
        /// </summary>
        /// <param name="sql">Sql</param>
        public override bool Script(DatabaseIndex index, StringBuilder sql, bool subObjects)
        {
            bool scriptCreated = false;

            if (!index.IsNew
                && index.NameIsEdited)
            {
                // change the index name
                sql.Append("DROP INDEX [");
                sql.Append(index.Table.Schema.Name);
                sql.Append("].[");
                sql.Append(index.Table.Name);
                sql.Append("].[");
                sql.Append(index.NameOriginal);
                sql.Append("]\nGO\n");

                scriptCreated = true;
            }

            if (index.IsNew
                || index.IsEdited
                || index.NameIsEdited)
            {
                sql.Append("CREATE ");
                if (index.IsUnique) { sql.Append("UNIQUE "); }
                sql.Append("NONCLUSTERED INDEX [");
                sql.Append(index.Name);
                sql.Append("] ON [");
                sql.Append(index.Table.Schema.Name);
                sql.Append("].[");
                sql.Append(index.Table.Name);
                sql.Append("] (");
                sql.Append(index.Columns);
                sql.Append(")WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ");
                sql.Append(index.IsNew || index.NameIsEdited ? "OFF" : "ON");
                sql.Append(", IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]\nGO\n");

                scriptCreated = true;
            }

            return scriptCreated;
        }

        #endregion

        #region Script a reference

        /// <summary>
        /// Generate script for this reference
        /// </summary>
        /// <param name="sql">Sql</param>
        public override bool Script(DatabaseReference reference, StringBuilder sql, bool subObjects)
        {
            bool scriptCreated = false;

            string triggerName = string.Format("{0}.{1}.{2}_{3}.{4}_DeleteAction",
                reference.Column.Table.Schema.Name, reference.Column.Table.Name, reference.Column.Name,
                reference.ReferencedTable.Schema.Name, reference.ReferencedTable.Name);
            string triggerNameRef = string.Format("{0}.{1}_{2}.{3}.{4}_DeleteAction",
                reference.ReferencedTable.Schema.Name, reference.ReferencedTable.Name,
                reference.Column.Table.Schema.Name, reference.Column.Table.Name, reference.Column.Name);

            if (!reference.IsNew
                && (reference.IsDeleted || reference.IsEdited || reference.NameIsEdited))
            {
                #region Drop the Foreign Key

                sql.Append("ALTER TABLE [");
                sql.Append(reference.Column.Table.Schema.Name);
                sql.Append("].[");
                sql.Append(reference.Column.Table.Name);
                sql.Append("] DROP CONSTRAINT [");
                sql.Append(reference.NameOriginal);
                sql.Append("]\nGO\n");

                #endregion

                #region Drop Extended Property

                sql.Append("sp_dropextendedproperty 'DeleteAction', 'schema', '");
                sql.Append(reference.Column.Table.Schema.Name);
                sql.Append("', 'table', '");
                sql.Append(reference.Column.Table.Name);
                sql.Append("', 'column', '");
                sql.Append(reference.Column.Name);
                sql.Append("'\nGO\n");

                #endregion

                #region Drop existing triggers

                sql.Append("IF EXISTS (SELECT * FROM sysobjects WHERE name='");
                sql.Append(triggerName);
                sql.Append("' AND type='TR') DROP TRIGGER [");
                sql.Append(reference.Column.Table.Schema.Name);
                sql.Append("].[");
                sql.Append(triggerName);
                sql.Append("]\nGO\n");

                sql.Append("IF EXISTS (SELECT * FROM sysobjects WHERE name='");
                sql.Append(triggerNameRef);
                sql.Append("' AND type='TR') DROP TRIGGER [");
                sql.Append(reference.ReferencedTable.Schema.Name);
                sql.Append("].[");
                sql.Append(triggerNameRef);
                sql.Append("]\nGO\n");

                #endregion

                scriptCreated = true;
            }

            if (!reference.IsDeleted
                && (reference.IsEdited || reference.NameIsEdited))
            {
                #region Add the Foreign Key

                sql.Append("ALTER TABLE [");
                sql.Append(reference.Column.Table.Schema.Name);
                sql.Append("].[");
                sql.Append(reference.Column.Table.Name);
                sql.Append("] WITH NOCHECK ADD CONSTRAINT [");
                sql.Append(reference.Name);
                sql.Append("] FOREIGN KEY([");
                sql.Append(reference.Column.Name);
                sql.Append("])REFERENCES [");
                sql.Append(reference.ReferencedTable.Schema.Name);
                sql.Append("].[");
                sql.Append(reference.ReferencedTable.Name);
                sql.Append("](Id) ON DELETE NO ACTION");
                sql.Append(" ON UPDATE NO ACTION\nGO\n");

                #endregion

                #region Disable Ref Integrity, done with triggers

                sql.Append("ALTER TABLE [");
                sql.Append(reference.Column.Table.Schema.Name);
                sql.Append("].[");
                sql.Append(reference.Column.Table.Name);
                sql.Append("] NOCHECK CONSTRAINT [");
                sql.Append(reference.Name);
                sql.Append("]\nGO\n");

                #endregion

                #region add Extended Property

                sql.Append("sp_addextendedproperty 'DeleteAction', '");
                sql.Append(reference.DeleteAction.ToString("G"));
                sql.Append("', 'schema', '");
                sql.Append(reference.Column.Table.Schema.Name);
                sql.Append("', 'table', '");
                sql.Append(reference.Column.Table.Name);
                sql.Append("', 'column', '");
                sql.Append(reference.Column.Name);
                sql.Append("'\nGO\n");

                #endregion

                #region create cascade to trigger

                if (reference.DeleteAction == DeleteActionEnum.CascadeTo)
                {
                    sql.Append("CREATE TRIGGER [");
                    sql.Append(triggerName);
                    sql.Append("] ON [");
                    sql.Append(reference.Column.Table.Schema.Name);
                    sql.Append("].[");
                    sql.Append(reference.Column.Table.Name);
                    sql.Append("] FOR DELETE AS\n-- Auto Generated, do not edit\nDELETE FROM [");
                    sql.Append(reference.ReferencedTable.Schema.Name);
                    sql.Append("].[");
                    sql.Append(reference.ReferencedTable.Name);
                    sql.Append("] WHERE Id IN (SELECT deleted.[");
                    sql.Append(reference.Column.Name);
                    sql.Append("] FROM deleted)\nGO\n");
                }

                #endregion

                #region create the trigger for the referenced table

                switch (reference.DeleteAction)
                {
                    case DeleteActionEnum.None:
                    case DeleteActionEnum.CascadeTo:
                        // disallow delete
                        sql.Append("CREATE TRIGGER [");
                        sql.Append(triggerNameRef);
                        sql.Append("] ON [");
                        sql.Append(reference.ReferencedTable.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.ReferencedTable.Name);
                        sql.Append("] FOR DELETE AS\n-- Auto Generated, do not edit\nIF EXISTS(SELECT * FROM [");
                        sql.Append(reference.Column.Table.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.Column.Table.Name);
                        sql.Append("] WHERE [");
                        sql.Append(reference.Column.Name);
                        sql.Append("] IN (SELECT deleted.Id FROM deleted)) BEGIN\n RAISERROR('Cannot delete from ");
                        sql.Append(reference.ReferencedTable.Schema.Name);
                        sql.Append(".");
                        sql.Append(reference.ReferencedTable.Name);
                        sql.Append(" related data exists in ");
                        sql.Append(reference.Column.Table.Schema.Name);
                        sql.Append(".");
                        sql.Append(reference.Column.Table.Name);
                        sql.Append(".', 16, 1)\n ROLLBACK TRANSACTION\nEND\nGO\n");

                        break;
                    case DeleteActionEnum.CascadeFrom:
                        sql.Append("CREATE TRIGGER [");
                        sql.Append(triggerNameRef);
                        sql.Append("] ON [");
                        sql.Append(reference.ReferencedTable.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.ReferencedTable.Name);
                        sql.Append("] FOR DELETE AS\n-- Auto Generated, do not edit\nDELETE FROM [");
                        sql.Append(reference.Column.Table.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.Column.Table.Name);
                        sql.Append("] WHERE [");
                        sql.Append(reference.Column.Name);
                        sql.Append("]IN (SELECT deleted.Id FROM deleted)\nGO\n");

                        break;
                    case DeleteActionEnum.ToNull:
                        sql.Append("CREATE TRIGGER [");
                        sql.Append(triggerNameRef);
                        sql.Append("] ON [");
                        sql.Append(reference.ReferencedTable.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.ReferencedTable.Name);
                        sql.Append("] FOR DELETE AS\n-- Auto Generated, do not edit\nUPDATE [");
                        sql.Append(reference.Column.Table.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.Column.Table.Name);
                        sql.Append("] SET [");
                        sql.Append(reference.Column.Name);
                        sql.Append("]=NULL WHERE [");
                        sql.Append(reference.Column.Name);
                        sql.Append("] IN (SELECT deleted.Id FROM deleted)\nGO\n");

                        break;
                    case DeleteActionEnum.ToDefault:
                        sql.Append("CREATE TRIGGER [");
                        sql.Append(triggerNameRef);
                        sql.Append("] ON [");
                        sql.Append(reference.ReferencedTable.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.ReferencedTable.Name);
                        sql.Append("] FOR DELETE AS\n-- Auto Generated, do not edit\nUPDATE [");
                        sql.Append(reference.Column.Table.Schema.Name);
                        sql.Append("].[");
                        sql.Append(reference.Column.Table.Name);
                        sql.Append("] SET [");
                        sql.Append(reference.Column.Name);
                        sql.Append("]=");
                        sql.Append(reference.Column.Default.Value);
                        sql.Append(" WHERE [");
                        sql.Append(reference.Column.Name);
                        sql.Append("] IN (SELECT deleted.Id FROM deleted)\nGO\n");

                        break;
                }

                #endregion

                scriptCreated = true;

            }

            return scriptCreated;
        }

        #endregion

        #region Script a table

        /// <summary>
        /// Generate script for this table
        /// </summary>
        /// <param name="sql">Sql</param>
        public override bool Script(DatabaseTable table, StringBuilder sql, bool subObjects)
        {
            bool scriptCreated = false;

            if (table.IsNew)
            {
                sql.Append("CREATE TABLE [");
                sql.Append(table.Schema.Name);
                sql.Append("].[");
                sql.Append(table.Name);
                sql.Append("](Id int NOT NULL IDENTITY (1, 1))\n ALTER TABLE [");
                sql.Append(table.Schema.Name);
                sql.Append("].[");
                sql.Append(table.Name);
                sql.Append("] ADD CONSTRAINT ");
                sql.Append(table.Name);
                sql.Append("_PrimaryKey PRIMARY KEY CLUSTERED(Id)\nGO\n");

                scriptCreated = true;
            }
            else if (table.IsEdited || table.NameIsEdited)
            {
                // check for different name
                if (table.NameIsEdited)
                {
                    sql.Append("EXECUTE sp_rename N'[");
                    sql.Append(table.SchemaOriginal.Name);
                    sql.Append("].[");
                    sql.Append(table.NameOriginal);
                    sql.Append("]', N'");
                    sql.Append(table.Name);
                    sql.Append("', 'OBJECT'\nGO\n");

                    scriptCreated = true;
                }

                // check for a different schema
                if (table.Schema != table.SchemaOriginal)
                {
                    sql.Append("ALTER SCHEMA [");
                    sql.Append(table.Schema.Name);
                    sql.Append("] TRANSFER [");
                    sql.Append(table.SchemaOriginal.Name);
                    sql.Append("].[");
                    sql.Append(table.Name);
                    sql.Append("]\nGO\n");

                    scriptCreated = true;
                }
            }

            if (subObjects)
            {
                if (table.Columns.Script(sql, subObjects))
                {
                    sql.Append("\nGO\n");

                    scriptCreated = true;
                }

                if (table.Indexes.Script(sql, subObjects))
                {
                    sql.Append("\nGO\n");

                    scriptCreated = true;
                }
            }

            return scriptCreated;
        }

        #endregion

        #region Script a column

        /// <summary>
        /// Generate script for this column
        /// </summary>
        /// <param name="sql">Sql</param>
        public override bool Script(DatabaseColumn column, StringBuilder sql, bool subObjects)
        {
            bool scriptCreated = false;

            if (column.Name != "Id")
            {
                if (column.IsNew)
                {
                    sql.Append("ALTER TABLE [");
                    sql.Append(column.Table.Schema.Name);
                    sql.Append("].[");
                    sql.Append(column.Table.Name);
                    sql.Append("] ADD [");
                    sql.Append(column.Name);
                    sql.Append("] ");
                    sql.Append(column.TypeName);
                    switch (column.TypeName)
                    {
                        case "char":
                        case "varchar":
                        case "binary":
                            sql.Append("(");
                            sql.Append(column.Length);
                            sql.Append(")");

                            break;
                    }
                    if (!column.AllowNull) { sql.Append(" NOT"); }
                    sql.Append(" NULL");
                    sql.Append("\n");

                    if (column.Default.Value != null)
                    {
                        sql.Append(" CONSTRAINT ");
                        sql.Append(column.Default.GetName(column));
                        sql.Append(" DEFAULT ");
                        sql.Append(column.Default.Value);
                        sql.Append("\n");
                    }

                    scriptCreated = true;
                }
                else
                {
                    if (column.NameIsEdited)
                    {
                        // change the column name
                        sql.Append("EXECUTE sp_rename N'[");
                        sql.Append(column.Table.Schema.Name);
                        sql.Append("].[");
                        sql.Append(column.Table.Name);
                        sql.Append("].[");
                        sql.Append(column.NameOriginal);
                        sql.Append("]', N'");
                        sql.Append(column.Name);
                        sql.Append("', 'COLUMN'\nGO\n");

                        scriptCreated = true;
                    }

                    if (column.IsEdited)
                    {
                        // drop default constraint to allow changes to the column to be made
                        if (!column.Default.IsNew)
                        {
                            sql.Append("ALTER TABLE [");
                            sql.Append(column.Table.Schema.Name);
                            sql.Append("].[");
                            sql.Append(column.Table.Name);
                            sql.Append("] DROP CONSTRAINT ");
                            sql.Append(column.Default.NameOriginal);
                            sql.Append("\nGO\n");

                            column.Default.IsNew = true;
                        }
                        // drop indexes dependant on this column
                        foreach (DatabaseIndex index in column.GetIndexes())
                        {
                            if (!index.IsNew)
                            {
                                // change the index name
                                sql.Append("DROP INDEX [");
                                sql.Append(column.Table.Schema.Name);
                                sql.Append("].[");
                                sql.Append(column.Table.Name);
                                sql.Append("].[");
                                sql.Append(column.NameOriginal);
                                sql.Append("]\n");

                                index.IsNew = true;
                            }
                        }

                        // update the column type
                        sql.Append("ALTER TABLE [");
                        sql.Append(column.Table.Schema.Name);
                        sql.Append("].[");
                        sql.Append(column.Table.Name);
                        sql.Append("] ALTER COLUMN [");
                        sql.Append(column.Name);
                        sql.Append("] ");
                        sql.Append(column.TypeName);
                        switch (column.TypeName)
                        {
                            case "char":
                            case "varchar":
                            case "binary":
                                sql.Append("(");
                                sql.Append(column.Length);
                                sql.Append(")");

                                break;
                        }
                        if (!column.AllowNull) { sql.Append(" NOT"); }
                        sql.Append(" NULL");
                        sql.Append("\n");

                        scriptCreated = true;
                    }

                    if (column.Default.IsEdited
                        || column.Default.NameOriginal != column.Default.GetName(column))
                    {
                        if (!column.Default.IsNew)
                        {
                            sql.Append("ALTER TABLE [");
                            sql.Append(column.Table.Schema.Name);
                            sql.Append("].[");
                            sql.Append(column.Table.Name);
                            sql.Append("] DROP CONSTRAINT ");
                            sql.Append(column.Default.NameOriginal);
                            sql.Append("\nGO\n");

                            scriptCreated = true;
                        }
                        if (column.Default.Value != null)
                        {
                            sql.Append("-- Old Default: ");
                            sql.Append(column.Default.ValueOriginal);
                            sql.Append("\n-- New Default: ");
                            sql.Append(column.Default.Value);
                            sql.Append("\n");

                            sql.Append("ALTER TABLE [");
                            sql.Append(column.Table.Schema.Name);
                            sql.Append("].[");
                            sql.Append(column.Table.Name);
                            sql.Append("] ADD CONSTRAINT ");
                            sql.Append(column.Default.GetName(column));
                            sql.Append(" DEFAULT ");
                            sql.Append(column.Default.Value);
                            sql.Append(" FOR [");
                            sql.Append(column.Name);
                            sql.Append("]\n");

                            scriptCreated = true;
                        }
                    }
                }
            }

            return scriptCreated;
        }

        #endregion

        #endregion

        #region Load

        #region Columns

        private string _SQLColumnsLoad =
@"SELECT col.name AS ColumnName, typ.name AS typeName, col.max_length AS length, col.is_nullable, def.name AS DefaultName, defcom.text AS DefaultValue
FROM sys.syscomments AS defcom 
    INNER JOIN sys.sysobjects AS def ON defcom.id = def.id 
    RIGHT OUTER JOIN sys.columns AS col 
        INNER JOIN sys.types AS typ ON col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    ON def.id = col.default_object_id
WHERE (col.object_id = OBJECT_ID('[TableName]'))
ORDER BY col.column_id";

        /// <summary>
        /// Get all columns in a table
        /// </summary>
        protected override void LoadColumns(DatabaseTable table)
        {
            if (!table.IsNew)
            {
                SqlConnection conn = new SqlConnection(this.ConnectionString);
                conn.Open();

                try
                {
                    table.Columns.Clear();

                    SqlCommand cmd = new SqlCommand(
                        _SQLColumnsLoad.Replace("[TableName]", "[" + table.Schema.NameOriginal + "].[" + table.NameOriginal + "]"),
                        conn);
                    SqlDataReader data = cmd.ExecuteReader();
                    try
                    {
                        while (data.Read())
                        {
                            this.LoadColumn(table,
                                data.GetString(0),
                                data.GetString(1), data.GetInt16(2), data.GetBoolean(3),
                                data.IsDBNull(4) ? null : data.GetString(4),
                                data.IsDBNull(5) ? null : TrimValue(data.GetString(5)));
                        }
                    }
                    finally { data.Close(); }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally { conn.Close(); }
            }
        }

        /// <summary>
        /// Remove starting and ending brackets
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private string TrimValue(string value)
        {
            if (value.StartsWith("(")) { value = value.Substring(1); }
            if (value.EndsWith(")")) { value = value.Substring(0, value.Length - 1); }

            return value;
        }

        #endregion

        #region Tables

        /// <summary>
        /// Get all tables on server
        /// </summary>
        protected override void LoadTables()
        {
            if (!this.IsNew)
            {
                SqlConnection conn = new SqlConnection(this.ConnectionString);
                conn.Open();

                try
                {
                    this.Tables.Clear();

                    DataTable dt = conn.GetSchema("TABLES");
                    foreach (DataRow dr in dt.Rows)
                    {
                        this.LoadTable((string)dr[1], (string)dr[2]);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally { conn.Close(); }
            }
        }

        #endregion

        #region Schemas

        /// <summary>
        /// Get all Schemas on server
        /// </summary>
        protected override void LoadSchemas()
        {
            if (!this.IsNew)
            {
                SqlConnection conn = new SqlConnection(this.ConnectionString);
                conn.Open();

                try
                {
                    this.Schemas.Clear();

                    SqlCommand cmd = new SqlCommand(
                        "SELECT Name FROM sys.schemas", conn);
                    SqlDataReader data = cmd.ExecuteReader();
                    while (data.Read())
                    {
                        this.LoadSchema(data.GetString(0));
                    }
                    data.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally { conn.Close(); }
            }
        }

        #endregion

        #region References

        private string _SQLReferenceLoad =
@"SELECT 
	fkey.[name], 
	SCHEMA_NAME(tbl.schema_id), OBJECT_NAME(tbl.object_id), COL_NAME(const.id, colid),
 	SCHEMA_NAME(ref.schema_id), OBJECT_NAME(ref.object_id),
    (SELECT TOP 1 value FROM ::fn_listextendedproperty ('DeleteAction', 'schema', SCHEMA_NAME(tbl.schema_id), 'table', OBJECT_NAME(tbl.object_id), 'column', COL_NAME(const.id, colid)))
FROM sys.foreign_keys fkey
	JOIN sys.tables tbl ON fkey.parent_object_id=tbl.object_id
	JOIN sys.tables ref ON fkey.referenced_object_id=ref.object_id
	JOIN sys.sysconstraints const ON fkey.object_id=const.constid";

        /// <summary>
        /// Get all columns in a table
        /// </summary>
        protected override void LoadReferences()
        {
            if (!this.IsNew)
            {
                SqlConnection conn = new SqlConnection(this.ConnectionString);
                conn.Open();

                try
                {
                    this.References.Clear();

                    SqlCommand cmd = new SqlCommand(_SQLReferenceLoad, conn);
                    SqlDataReader data = cmd.ExecuteReader();
                    try
                    {
                        while (data.Read())
                        {
                            this.LoadReference(
                                data.GetString(0),
                                this.Tables[data.GetString(1), data.GetString(2)].Columns[data.GetString(3)],
                                this.Tables[data.GetString(4), data.GetString(5)],
                                data.IsDBNull(6)
                                    ? DeleteActionEnum.NotSet
                                    : (DeleteActionEnum)Enum.Parse(typeof(DeleteActionEnum), data.GetString(6)));
                        }
                    }
                    finally { data.Close(); }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally { conn.Close(); }
            }
        }

        #endregion

        #region Indexes

        private string _SQLLoadIndexes =
@"SELECT 
	ind.name,
	MAX(CASE WHEN indCol.key_ordinal = 1 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END)
	+ CASE WHEN MAX(CASE WHEN indCol.key_ordinal = 2 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END) IS NOT NULL THEN
		',' + MAX(CASE WHEN indCol.key_ordinal = 2 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END)
		ELSE '' END
	+ CASE WHEN MAX(CASE WHEN indCol.key_ordinal = 3 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END) IS NOT NULL THEN
		',' + MAX(CASE WHEN indCol.key_ordinal = 3 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END)
		ELSE '' END
	+ CASE WHEN MAX(CASE WHEN indCol.key_ordinal = 4 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END) IS NOT NULL THEN
		',' + MAX(CASE WHEN indCol.key_ordinal = 4 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END)
		ELSE '' END
	+ CASE WHEN MAX(CASE WHEN indCol.key_ordinal = 5 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END) IS NOT NULL THEN
		',' + MAX(CASE WHEN indCol.key_ordinal = 5 THEN CASE WHEN indCol.is_descending_key=1 THEN col.Name+' DESC' ELSE col.Name END END)
		ELSE '' END AS Columns, 
	ind.is_unique
FROM sys.indexes AS ind
	LEFT JOIN sys.index_columns AS indCol ON ind.object_id = indCol.object_id AND ind.index_id = indCol.index_id 
	LEFT JOIN sys.columns AS col ON col.object_id = indCol.object_id AND col.column_id = indCol.column_id
WHERE
	ind.[type]=2
	AND ind.object_id=OBJECT_ID('[TableName]')
GROUP BY ind.name, ind.is_unique";

        /// <summary>
        /// Get all indexes in a table
        /// </summary>
        protected override void LoadIndexes(DatabaseTable table)
        {
            if (!table.IsNew)
            {
                SqlConnection conn = new SqlConnection(this.ConnectionString);
                conn.Open();

                try
                {
                    table.Indexes.Clear();

                    SqlCommand cmd = new SqlCommand(
                        _SQLLoadIndexes.Replace("[TableName]", "[" + table.Schema.NameOriginal + "].[" + table.NameOriginal + "]"),
                        conn);
                    SqlDataReader data = cmd.ExecuteReader();
                    try
                    {
                        while (data.Read())
                        {
                            this.LoadIndex(table,
                                data.GetString(0),
                                data.GetString(1), data.GetBoolean(2));
                        }
                    }
                    finally { data.Close(); }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally { conn.Close(); }
            }
        }

        #endregion

        #endregion

        #region SetTypeToColumn

        /// <summary>
        /// <para>Set the column properties according to the c# type passed</para>
        /// </summary>
        public override void SetTypeToColumn(
            Type type,
            int length, bool variableLength,
            string defaultValue,
            DatabaseColumn column)
        {
            switch (Type.GetTypeCode(type))
            {
                case TypeCode.Boolean:
                    column.TypeName = "bit"; break;
                case TypeCode.Byte:
                case TypeCode.SByte:
                    column.TypeName = "tinyint"; break;
                case TypeCode.Int16:
                    column.TypeName = "smallint"; break;
                case TypeCode.Int32:
                    column.TypeName = "int"; break;
                case TypeCode.Int64:
                    column.TypeName = "bigint"; break;
                case TypeCode.Single:
                    column.TypeName = "real"; break;
                case TypeCode.Double:
                case TypeCode.Decimal:
                    column.TypeName = "decimal"; break;
                case TypeCode.DateTime:
                    column.TypeName = "datetime"; break;
                case TypeCode.String:
                    if (length > 8000)
                    {
                        column.TypeName = "text";
                    }
                    else
                    {
                        column.TypeName = variableLength ? "varchar" : "char";
                        column.Length = length;
                    }

                    break;
                case TypeCode.Object:
                    switch (type.Name)
                    {
                        case "Binary":
                            if (length > 8000)
                            {
                                column.TypeName = "image";
                            }
                            else
                            {
                                column.TypeName = "binary";
                                column.Length = length;
                            }
                            break;
                        default:
                            throw new Exception(string.Format("Data type {0} not expected for column {1}", type.FullName, column.Name));
                    }
                    break;
                default:
                    throw new Exception(string.Format("Data type {0} not expected for column {1}", type.FullName, column.Name));
            }

            // Set the default
            if (defaultValue == null)
            {
                switch (column.TypeName)
                {
                    case "bit":
                    case "tinyint":
                    case "smallint":
                    case "int":
                    case "bigint":
                    case "real":
                    case "decimal": column.Default.Value = column.AllowNull ? null : "(0)"; break;
                    case "datetime": column.Default.Value = column.AllowNull ? null : "((1)-(1))-(1)"; break;
                    default: column.Default.Value = column.AllowNull ? null : "''"; break;
                }
            }
            else { column.Default.Value = defaultValue; }
        }

        #endregion

        #endregion
    }
}